﻿Imports System.Data.OleDb

Public Class LopDAO
    Public Shared Function SelectAll() As List(Of LopDTO)
        Const strSql As String = "select * from LOP"
        Dim ds = New List(Of LopDTO)
        Using db = New SQLProvider()
            Using re = db.ExecuteReader(strSql)
                While re.Read()
                    Dim obj = New LopDTO
                    obj.MALOP = re.GetInt32(0)
                    obj.TENLOP = re.GetString(1)
                    obj.SISO = re.GetString(2)
                    obj.KHOI = re.GetString(3)
                    obj.NAM = re.GetString(4)
                    obj.MAKHOI = re.GetInt32(5)
                    ds.Add(obj)
                End While
            End Using
        End Using

        Return ds
    End Function

    Public Shared Function LayDSLopHoc() As List(Of LopDTO)
        Dim str = "select * from LOP"
        Dim conn = SQLProvider.ConnectDB("QuanLiHocSinh.mdb")
        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of LopDTO)

        While reader.Read()
            Dim hs As New LopDTO
            hs.MALOP = reader.GetInt32(0)
            hs.TENLOP = reader.GetString(1)
            hs.SISO = reader.GetString(2)
            hs.KHOI = reader.GetString(3)
            hs.NAM = reader.GetString(4)
            'hs.SODT = reader.GetString(5)
            hs.MAKHOI = reader.GetInt32(5)
            'hs.MALOP = reader.GetInt32(7)

            ds.Add(hs)
        End While
        conn.Close()
        Return ds
    End Function

    Public Shared Function SuaLopHoc(ByVal lop As LopDTO) As Integer
        Dim str = String.Format("update LOP set TENLOP='{0}', SISO='{1}', KHOI='{2}', NAM='{3}', MAKHOI={4} where MALOP={5}", lop.TENLOP, lop.SISO, lop.KHOI, lop.NAM, lop.MAKHOI, lop.MALOP)
        Dim conn = SQLProvider.ConnectDB("QuanLiHocSinh.mdb")
        Dim cmd As New OleDbCommand(Str, conn)
        Dim result = -1
        result = cmd.ExecuteNonQuery()
        conn.Close()
        Return result
    End Function

    Public Shared Function SelectByMaKhoi(ByVal MaKhoi As Integer) As List(Of LopDTO)
        Dim strSql As String = String.Format("select MALOP,TENLOP, SISO, KHOI, NAM, MAKHOI from LOP where MaKhoi = {0}", MaKhoi)
        Dim ds = New List(Of LopDTO)
        Using db = New SQLProvider()
            Using re = db.ExecuteReader(strSql)
                While re.Read()
                    Dim obj = New LopDTO
                    'obj.MALOP = re.GetString(0)
                    obj.MALOP = re.GetInt32(0)
                    obj.TENLOP = re.GetString(1)
                    obj.SISO = re.GetString(2)
                    obj.KHOI = re.GetString(3)
                    obj.NAM = re.GetString(4)
                    'obj.SODT = re.GetString(5)
                    'obj.EMAIL = re.GetString(6)
                    obj.MAKHOI = re.GetInt32(5)
                    ds.Add(obj)
                End While
            End Using
        End Using

        Return ds
    End Function

    Public Shared Function DemSoHSTheoLop() As List(Of LopDTO)
        Dim str = "select l.MALOP, TENLOP, SISO, KHOI, NAM, MAKHOI ,count(h.MAHOCSINH) "
        str &= "from LOP l, HOCSINH h where l.MALOP=h.MALOP "
        str &= "group by l.MALOP, TENLOP, SISO, KHOI, NAM, MAKHOI"
        Dim conn = SQLProvider.ConnectDB("QuanLiHocSinh.mdb")
        Dim cmd As New OleDbCommand(str, conn)
        Dim reader = cmd.ExecuteReader()
        Dim ds As New List(Of LopDTO)

        While reader.Read()
            Dim obj As New LopDTO
            obj.MALOP = reader.GetInt32(0)
            obj.TENLOP = reader.GetString(1)
            obj.SISO = reader.GetString(2)
            obj.KHOI = reader.GetString(3)
            obj.NAM = reader.GetString(4)
            obj.MAKHOI = reader.GetInt32(5)
            obj.SOLUONGHS = reader.GetInt32(6)

            ds.Add(obj)
        End While
        conn.Close()
        Return ds
    End Function
End Class
